﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using Agresso.Interface.Layer.Gateway;
using System.Collections;
using System.Data.SqlClient;
using Agresso.Interface.Layer.SQL;
using Agresso.Interface.Layer.Domain;

namespace Agresso.Interface.Layer.Service
{
    public class AGRESO_SYNCService
    {
        DataBaseConnectivity m_dataBaseConectivity;
        public AGRESO_SYNCService()
        {
            m_dataBaseConectivity = new DataBaseConnectivity();
        }
        public IList GetAllPending()
        {
            try
            {
                m_dataBaseConectivity.CrewPayrollSqlConnection.Open();
                IList result = new ArrayList();
                SqlCommand cmd = new SqlCommand(CompasSQL.GetAllPendingAGRESO_SYNC(), m_dataBaseConectivity.CrewPayrollSqlConnection);
                SqlDataReader rd = cmd.ExecuteReader();
                while (rd.Read())
                    result.Add(new AGRESO_SYNC(rd));
                rd.Close();
                return result;
            }
            catch(Exception ex)
            {
                throw (ex);
            }
            finally
            {
                m_dataBaseConectivity.CrewPayrollSqlConnection.Close();
            }
        }

        public IList GetAllPending(int month, int year, string[] vesselname, bool exported, int AS_COMPANY_CODE)
        {
            try
            {
                m_dataBaseConectivity.CrewPayrollSqlConnection.Open();
                IList result = new ArrayList();
                SqlCommand cmd = new SqlCommand(CompasSQL.GetAllPendingAGRESO_SYNC(month,year,vesselname,exported),
                    m_dataBaseConectivity.CrewPayrollSqlConnection);
                SqlDataReader rd = cmd.ExecuteReader();
                while (rd.Read())
                {
                    AGRESO_SYNC crew = new AGRESO_SYNC(rd);
                    if (crew.AS_COMPANY_CODE == "")
                        crew.AS_COMPANY_CODE = AS_COMPANY_CODE.ToString();
                    result.Add(crew);
                }
                rd.Close();
                return result;
            }
            catch (Exception ex)
            {
                throw (ex);
            }
            finally
            {
                m_dataBaseConectivity.CrewPayrollSqlConnection.Close();
            }
        }
        
        public IList GetAllPendingStaging()
        {
            try
            {
                m_dataBaseConectivity.CrewPayrollStagingSqlConnection.Open();
                IList result = new ArrayList();
                SqlCommand cmd = new SqlCommand(CompasSQL.GetAllPendingStagingAGRESO_SYNC(), m_dataBaseConectivity.CrewPayrollStagingSqlConnection);
                SqlDataReader rd = cmd.ExecuteReader();
                while (rd.Read())
                    result.Add(new AGRESO_SYNC(rd));
                rd.Close();
                return result;
            }
            catch (Exception ex)
            {
                throw (ex);
            }
            finally
            {
                m_dataBaseConectivity.CrewPayrollStagingSqlConnection.Close();
            }
        }
        public void UpdateStatus(int id, int status)//, DateTime date)
        {
            try
            {
                m_dataBaseConectivity.CrewPayrollSqlConnection.Open();
                SqlCommand cmd = new SqlCommand(CompasSQL.UpdateAGRESO_SYNCStatus(), m_dataBaseConectivity.CrewPayrollSqlConnection);
                cmd.Parameters.Add("@AS_IS_EXPORTED", System.Data.SqlDbType.Bit).Value = status;
                //cmd.Parameters.Add("@AS_PICKUP_DATE_TIME", System.Data.SqlDbType.NVarChar).Value = date.ToString("yyyyMMdd HH:mm");
                cmd.Parameters.Add("@AS_ID", System.Data.SqlDbType.Int).Value = id;
                cmd.ExecuteNonQuery();
            }
            catch (Exception ex)
            {
                throw (ex);
            }
            finally
            {
                m_dataBaseConectivity.CrewPayrollSqlConnection.Close();
            }
        }

        public void UpdateStagingStatus(int id, int status)//, DateTime date)
        {
            try
            {
                m_dataBaseConectivity.CrewPayrollStagingSqlConnection.Open();
                SqlCommand cmd = new SqlCommand(CompasSQL.UpdateAGRESO_SYNCStatus(), m_dataBaseConectivity.CrewPayrollStagingSqlConnection);
                cmd.Parameters.Add("@AS_IS_EXPORTED", System.Data.SqlDbType.Bit).Value = status;
                //cmd.Parameters.Add("@AS_PICKUP_DATE_TIME", System.Data.SqlDbType.NVarChar).Value = date.ToString("yyyyMMdd HH:mm");
                cmd.Parameters.Add("@AS_ID", System.Data.SqlDbType.Int).Value = id;
                cmd.ExecuteNonQuery();
            }
            catch (Exception ex)
            {
                throw (ex);
            }
            finally
            {
                m_dataBaseConectivity.CrewPayrollStagingSqlConnection.Close();
            }
        }

        public AGRESO_SYNC GetByID(int id)
        {
            try
            {
                m_dataBaseConectivity.CrewPayrollSqlConnection.Open();
                AGRESO_SYNC result = null;
                SqlCommand cmd = new SqlCommand(CompasSQL.GetAllAGRESO_SYNCByID(), m_dataBaseConectivity.CrewPayrollSqlConnection);
                cmd.Parameters.Add("@AS_ID", System.Data.SqlDbType.Int).Value = id;
                SqlDataReader rd = cmd.ExecuteReader();
                while (rd.Read())
                    result = new AGRESO_SYNC(rd);
                rd.Close();
                return result;
            }
            catch (Exception ex)
            {
                throw (ex);
            }
            finally
            {
                m_dataBaseConectivity.CrewPayrollSqlConnection.Close();
            }
        }
        public IList GetAllPendingFromMiddleLayer()
        {
            try
            {
                m_dataBaseConectivity.InterfaceSqlConnection.Open();
                IList result = new ArrayList();
                SqlCommand cmd = new SqlCommand(CompasSQL.GetAllPendingFromIntermediate(), m_dataBaseConectivity.InterfaceSqlConnection);
                SqlDataReader rd = cmd.ExecuteReader();
                while (rd.Read())
                    result.Add(new AGRESO_SYNC(rd));
                rd.Close();
                return result;
            }
            catch (Exception ex)
            {
                throw (ex);
            }
            finally
            {
                m_dataBaseConectivity.InterfaceSqlConnection.Close();
            }
        }
    }
}
